{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5e1f1602",
   "metadata": {},
   "source": [
    "# Тестовые задачи от клаудов"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5d469d2",
   "metadata": {},
   "source": [
    "Приветсвую, коллеги. Ну и задачки у вас, прямо скажем. Такое только в корону решать."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "97dbc2c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install ipython-sql\n",
    "#!pip install sqlalchemy\n",
    "#!pip install psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "39bafeb5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql\n",
    "from sqlalchemy import create_engine\n",
    "from IPython.display import Image\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "90f41d69",
   "metadata": {},
   "outputs": [],
   "source": [
    "#подключаемся к базе\n",
    "%sql postgresql://postgres:12345678@localhost/tested"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2b83803",
   "metadata": {},
   "source": [
    "#### получились вот такие схемы, проверяем форматы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "b2157a56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>column_name</th>\n",
       "        <th>ordinal_position</th>\n",
       "        <th>column_default</th>\n",
       "        <th>is_nullable</th>\n",
       "        <th>data_type</th>\n",
       "        <th>character_maximum_length</th>\n",
       "        <th>character_octet_length</th>\n",
       "        <th>numeric_precision</th>\n",
       "        <th>numeric_precision_radix</th>\n",
       "        <th>numeric_scale</th>\n",
       "        <th>datetime_precision</th>\n",
       "        <th>interval_type</th>\n",
       "        <th>interval_precision</th>\n",
       "        <th>character_set_catalog</th>\n",
       "        <th>character_set_schema</th>\n",
       "        <th>character_set_name</th>\n",
       "        <th>collation_catalog</th>\n",
       "        <th>collation_schema</th>\n",
       "        <th>collation_name</th>\n",
       "        <th>domain_catalog</th>\n",
       "        <th>domain_schema</th>\n",
       "        <th>domain_name</th>\n",
       "        <th>udt_catalog</th>\n",
       "        <th>udt_schema</th>\n",
       "        <th>udt_name</th>\n",
       "        <th>scope_catalog</th>\n",
       "        <th>scope_schema</th>\n",
       "        <th>scope_name</th>\n",
       "        <th>maximum_cardinality</th>\n",
       "        <th>dtd_identifier</th>\n",
       "        <th>is_self_referencing</th>\n",
       "        <th>is_identity</th>\n",
       "        <th>identity_generation</th>\n",
       "        <th>identity_start</th>\n",
       "        <th>identity_increment</th>\n",
       "        <th>identity_maximum</th>\n",
       "        <th>identity_minimum</th>\n",
       "        <th>identity_cycle</th>\n",
       "        <th>is_generated</th>\n",
       "        <th>generation_expression</th>\n",
       "        <th>is_updatable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>customer</td>\n",
       "        <td>customer_id</td>\n",
       "        <td>1</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>integer</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>32</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>int4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>customer</td>\n",
       "        <td>name</td>\n",
       "        <td>2</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>character varying</td>\n",
       "        <td>100</td>\n",
       "        <td>400</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>customer</td>\n",
       "        <td>start_dttm</td>\n",
       "        <td>3</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>timestamp</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>customer</td>\n",
       "        <td>birth_dt</td>\n",
       "        <td>4</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>date</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>date</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('tested', 'public', 'customer', 'customer_id', 1, None, 'YES', 'integer', None, None, 32, 2, 0, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'int4', None, None, None, None, '1', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'customer', 'name', 2, None, 'YES', 'character varying', 100, 400, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'varchar', None, None, None, None, '2', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'customer', 'start_dttm', 3, None, 'YES', 'timestamp without time zone', None, None, None, None, None, 6, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'timestamp', None, None, None, None, '3', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'customer', 'birth_dt', 4, None, 'YES', 'date', None, None, None, None, None, 0, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'date', None, None, None, None, '4', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES')]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM information_schema.columns\n",
    "WHERE table_schema = 'public' AND table_name = 'customer'\n",
    "ORDER BY ordinal_position;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "123cddf4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>column_name</th>\n",
       "        <th>ordinal_position</th>\n",
       "        <th>column_default</th>\n",
       "        <th>is_nullable</th>\n",
       "        <th>data_type</th>\n",
       "        <th>character_maximum_length</th>\n",
       "        <th>character_octet_length</th>\n",
       "        <th>numeric_precision</th>\n",
       "        <th>numeric_precision_radix</th>\n",
       "        <th>numeric_scale</th>\n",
       "        <th>datetime_precision</th>\n",
       "        <th>interval_type</th>\n",
       "        <th>interval_precision</th>\n",
       "        <th>character_set_catalog</th>\n",
       "        <th>character_set_schema</th>\n",
       "        <th>character_set_name</th>\n",
       "        <th>collation_catalog</th>\n",
       "        <th>collation_schema</th>\n",
       "        <th>collation_name</th>\n",
       "        <th>domain_catalog</th>\n",
       "        <th>domain_schema</th>\n",
       "        <th>domain_name</th>\n",
       "        <th>udt_catalog</th>\n",
       "        <th>udt_schema</th>\n",
       "        <th>udt_name</th>\n",
       "        <th>scope_catalog</th>\n",
       "        <th>scope_schema</th>\n",
       "        <th>scope_name</th>\n",
       "        <th>maximum_cardinality</th>\n",
       "        <th>dtd_identifier</th>\n",
       "        <th>is_self_referencing</th>\n",
       "        <th>is_identity</th>\n",
       "        <th>identity_generation</th>\n",
       "        <th>identity_start</th>\n",
       "        <th>identity_increment</th>\n",
       "        <th>identity_maximum</th>\n",
       "        <th>identity_minimum</th>\n",
       "        <th>identity_cycle</th>\n",
       "        <th>is_generated</th>\n",
       "        <th>generation_expression</th>\n",
       "        <th>is_updatable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>account</td>\n",
       "        <td>account_id</td>\n",
       "        <td>1</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>integer</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>32</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>int4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>account</td>\n",
       "        <td>customer_id</td>\n",
       "        <td>2</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>integer</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>32</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>int4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>account</td>\n",
       "        <td>open_dt</td>\n",
       "        <td>3</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>date</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>date</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>account</td>\n",
       "        <td>close_dt</td>\n",
       "        <td>4</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>date</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>date</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('tested', 'public', 'account', 'account_id', 1, None, 'YES', 'integer', None, None, 32, 2, 0, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'int4', None, None, None, None, '1', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'account', 'customer_id', 2, None, 'YES', 'integer', None, None, 32, 2, 0, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'int4', None, None, None, None, '2', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'account', 'open_dt', 3, None, 'YES', 'date', None, None, None, None, None, 0, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'date', None, None, None, None, '3', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'account', 'close_dt', 4, None, 'YES', 'date', None, None, None, None, None, 0, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'date', None, None, None, None, '4', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES')]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "\n",
    "SELECT *\n",
    "FROM information_schema.columns\n",
    "WHERE table_schema = 'public' AND table_name = 'account'\n",
    "ORDER BY ordinal_position;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "d46e4960",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>table_catalog</th>\n",
       "        <th>table_schema</th>\n",
       "        <th>table_name</th>\n",
       "        <th>column_name</th>\n",
       "        <th>ordinal_position</th>\n",
       "        <th>column_default</th>\n",
       "        <th>is_nullable</th>\n",
       "        <th>data_type</th>\n",
       "        <th>character_maximum_length</th>\n",
       "        <th>character_octet_length</th>\n",
       "        <th>numeric_precision</th>\n",
       "        <th>numeric_precision_radix</th>\n",
       "        <th>numeric_scale</th>\n",
       "        <th>datetime_precision</th>\n",
       "        <th>interval_type</th>\n",
       "        <th>interval_precision</th>\n",
       "        <th>character_set_catalog</th>\n",
       "        <th>character_set_schema</th>\n",
       "        <th>character_set_name</th>\n",
       "        <th>collation_catalog</th>\n",
       "        <th>collation_schema</th>\n",
       "        <th>collation_name</th>\n",
       "        <th>domain_catalog</th>\n",
       "        <th>domain_schema</th>\n",
       "        <th>domain_name</th>\n",
       "        <th>udt_catalog</th>\n",
       "        <th>udt_schema</th>\n",
       "        <th>udt_name</th>\n",
       "        <th>scope_catalog</th>\n",
       "        <th>scope_schema</th>\n",
       "        <th>scope_name</th>\n",
       "        <th>maximum_cardinality</th>\n",
       "        <th>dtd_identifier</th>\n",
       "        <th>is_self_referencing</th>\n",
       "        <th>is_identity</th>\n",
       "        <th>identity_generation</th>\n",
       "        <th>identity_start</th>\n",
       "        <th>identity_increment</th>\n",
       "        <th>identity_maximum</th>\n",
       "        <th>identity_minimum</th>\n",
       "        <th>identity_cycle</th>\n",
       "        <th>is_generated</th>\n",
       "        <th>generation_expression</th>\n",
       "        <th>is_updatable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>transaction</td>\n",
       "        <td>transaction_id</td>\n",
       "        <td>1</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>integer</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>32</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>int4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>1</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>transaction</td>\n",
       "        <td>customer_id</td>\n",
       "        <td>2</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>integer</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>32</td>\n",
       "        <td>2</td>\n",
       "        <td>0</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>int4</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>2</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>transaction</td>\n",
       "        <td>amount_rur</td>\n",
       "        <td>3</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>2</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>numeric</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>3</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>transaction</td>\n",
       "        <td>success_flg</td>\n",
       "        <td>4</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>boolean</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>bool</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>4</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>transaction</td>\n",
       "        <td>transaction_dttm</td>\n",
       "        <td>5</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>timestamp without time zone</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>timestamp</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>5</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>tested</td>\n",
       "        <td>public</td>\n",
       "        <td>transaction</td>\n",
       "        <td>type</td>\n",
       "        <td>6</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "        <td>character varying</td>\n",
       "        <td>3</td>\n",
       "        <td>12</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>tested</td>\n",
       "        <td>pg_catalog</td>\n",
       "        <td>varchar</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>6</td>\n",
       "        <td>NO</td>\n",
       "        <td>NO</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>NO</td>\n",
       "        <td>NEVER</td>\n",
       "        <td>None</td>\n",
       "        <td>YES</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('tested', 'public', 'transaction', 'transaction_id', 1, None, 'YES', 'integer', None, None, 32, 2, 0, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'int4', None, None, None, None, '1', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'transaction', 'customer_id', 2, None, 'YES', 'integer', None, None, 32, 2, 0, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'int4', None, None, None, None, '2', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'transaction', 'amount_rur', 3, None, 'YES', 'numeric', None, None, 10, 10, 2, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'numeric', None, None, None, None, '3', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'transaction', 'success_flg', 4, None, 'YES', 'boolean', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'bool', None, None, None, None, '4', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'transaction', 'transaction_dttm', 5, None, 'YES', 'timestamp without time zone', None, None, None, None, None, 6, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'timestamp', None, None, None, None, '5', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES'),\n",
       " ('tested', 'public', 'transaction', 'type', 6, None, 'YES', 'character varying', 3, 12, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, 'tested', 'pg_catalog', 'varchar', None, None, None, None, '6', 'NO', 'NO', None, None, None, None, None, 'NO', 'NEVER', None, 'YES')]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT *\n",
    "FROM information_schema.columns\n",
    "WHERE table_schema = 'public' AND table_name = 'transaction'\n",
    "ORDER BY ordinal_position;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42f7b3d1",
   "metadata": {},
   "source": [
    "####  посмотрим данные которые я занес"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "68969186",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>name</th>\n",
       "        <th>start_dttm</th>\n",
       "        <th>birth_dt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Сергей</td>\n",
       "        <td>2011-07-01 00:00:00</td>\n",
       "        <td>1992-11-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Оксана</td>\n",
       "        <td>2016-07-01 00:00:00</td>\n",
       "        <td>1999-12-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>Василий</td>\n",
       "        <td>2017-03-12 00:00:00</td>\n",
       "        <td>1989-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>104</td>\n",
       "        <td>Семен</td>\n",
       "        <td>2022-07-01 00:00:00</td>\n",
       "        <td>2001-07-07</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(100, 'Сергей', datetime.datetime(2011, 7, 1, 0, 0), datetime.date(1992, 11, 7)),\n",
       " (101, 'Оксана', datetime.datetime(2016, 7, 1, 0, 0), datetime.date(1999, 12, 6)),\n",
       " (103, 'Василий', datetime.datetime(2017, 3, 12, 0, 0), datetime.date(1989, 11, 3)),\n",
       " (104, 'Семен', datetime.datetime(2022, 7, 1, 0, 0), datetime.date(2001, 7, 7))]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "80c8542a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>account_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>open_dt</th>\n",
       "        <th>close_dt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6768592</td>\n",
       "        <td>100</td>\n",
       "        <td>2011-07-01</td>\n",
       "        <td>2011-09-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7373212</td>\n",
       "        <td>101</td>\n",
       "        <td>2016-07-01</td>\n",
       "        <td>2017-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8431435</td>\n",
       "        <td>103</td>\n",
       "        <td>2017-03-12</td>\n",
       "        <td>2020-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8800555</td>\n",
       "        <td>104</td>\n",
       "        <td>2022-07-01</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6768592, 100, datetime.date(2011, 7, 1), datetime.date(2011, 9, 1)),\n",
       " (7373212, 101, datetime.date(2016, 7, 1), datetime.date(2017, 8, 1)),\n",
       " (8431435, 103, datetime.date(2017, 3, 12), datetime.date(2020, 3, 1)),\n",
       " (8800555, 104, datetime.date(2022, 7, 1), None)]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "11985fbf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>amount_rur</th>\n",
       "        <th>success_flg</th>\n",
       "        <th>transaction_dttm</th>\n",
       "        <th>type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345678</td>\n",
       "        <td>100</td>\n",
       "        <td>1200.00</td>\n",
       "        <td>True</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>BHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392341</td>\n",
       "        <td>101</td>\n",
       "        <td>14000.00</td>\n",
       "        <td>False</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>HNM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392342</td>\n",
       "        <td>101</td>\n",
       "        <td>8000.00</td>\n",
       "        <td>False</td>\n",
       "        <td>2016-08-03 06:02:30</td>\n",
       "        <td>FGN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392343</td>\n",
       "        <td>101</td>\n",
       "        <td>7777.00</td>\n",
       "        <td>False</td>\n",
       "        <td>2016-08-03 07:02:30</td>\n",
       "        <td>HRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345112</td>\n",
       "        <td>103</td>\n",
       "        <td>2346.00</td>\n",
       "        <td>True</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>HRN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1488.00</td>\n",
       "        <td>True</td>\n",
       "        <td>2017-04-12 08:02:30</td>\n",
       "        <td>WTF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1703.00</td>\n",
       "        <td>True</td>\n",
       "        <td>2018-04-12 08:02:30</td>\n",
       "        <td>BTR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345679</td>\n",
       "        <td>100</td>\n",
       "        <td>10000.00</td>\n",
       "        <td>True</td>\n",
       "        <td>2011-07-04 01:02:30</td>\n",
       "        <td>FWB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345680</td>\n",
       "        <td>100</td>\n",
       "        <td>3000.00</td>\n",
       "        <td>False</td>\n",
       "        <td>2011-07-05 01:02:30</td>\n",
       "        <td>WTF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99911135</td>\n",
       "        <td>104</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>True</td>\n",
       "        <td>2022-07-03 04:04:04</td>\n",
       "        <td>ZMR</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12345678, 100, Decimal('1200.00'), True, datetime.datetime(2011, 7, 3, 1, 1, 30), 'BHL'),\n",
       " (48392341, 101, Decimal('14000.00'), False, datetime.datetime(2016, 7, 3, 2, 2, 30), 'HNM'),\n",
       " (48392342, 101, Decimal('8000.00'), False, datetime.datetime(2016, 8, 3, 6, 2, 30), 'FGN'),\n",
       " (48392343, 101, Decimal('7777.00'), False, datetime.datetime(2016, 8, 3, 7, 2, 30), 'HRN'),\n",
       " (39345112, 103, Decimal('2346.00'), True, datetime.datetime(2017, 4, 12, 7, 2, 30), 'HRN'),\n",
       " (39345113, 103, Decimal('1488.00'), True, datetime.datetime(2017, 4, 12, 8, 2, 30), 'WTF'),\n",
       " (39345113, 103, Decimal('1703.00'), True, datetime.datetime(2018, 4, 12, 8, 2, 30), 'BTR'),\n",
       " (12345679, 100, Decimal('10000.00'), True, datetime.datetime(2011, 7, 4, 1, 2, 30), 'FWB'),\n",
       " (12345680, 100, Decimal('3000.00'), False, datetime.datetime(2011, 7, 5, 1, 2, 30), 'WTF'),\n",
       " (99911135, 104, Decimal('100000.00'), True, datetime.datetime(2022, 7, 3, 4, 4, 4), 'ZMR')]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from transaction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aebff074",
   "metadata": {},
   "source": [
    "#### Задача 1\n",
    "Для каждой транзакции посчитать количество дней, которые прошли с момента первой транзакции по этому клиенту. \n",
    "На выходе: transaction_id, customer_id, amount_rur, transaction_dttm, days_count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "e8c7afc9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>amount_rur</th>\n",
       "        <th>transaction_dttm</th>\n",
       "        <th>days_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345678</td>\n",
       "        <td>100</td>\n",
       "        <td>1200.00</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345679</td>\n",
       "        <td>100</td>\n",
       "        <td>10000.00</td>\n",
       "        <td>2011-07-04 01:02:30</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345680</td>\n",
       "        <td>100</td>\n",
       "        <td>3000.00</td>\n",
       "        <td>2011-07-05 01:02:30</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392341</td>\n",
       "        <td>101</td>\n",
       "        <td>14000.00</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392342</td>\n",
       "        <td>101</td>\n",
       "        <td>8000.00</td>\n",
       "        <td>2016-08-03 06:02:30</td>\n",
       "        <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392343</td>\n",
       "        <td>101</td>\n",
       "        <td>7777.00</td>\n",
       "        <td>2016-08-03 07:02:30</td>\n",
       "        <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345112</td>\n",
       "        <td>103</td>\n",
       "        <td>2346.00</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1488.00</td>\n",
       "        <td>2017-04-12 08:02:30</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1703.00</td>\n",
       "        <td>2018-04-12 08:02:30</td>\n",
       "        <td>365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99911135</td>\n",
       "        <td>104</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>2022-07-03 04:04:04</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12345678, 100, Decimal('1200.00'), datetime.datetime(2011, 7, 3, 1, 1, 30), 0.0),\n",
       " (12345679, 100, Decimal('10000.00'), datetime.datetime(2011, 7, 4, 1, 2, 30), 1.0),\n",
       " (12345680, 100, Decimal('3000.00'), datetime.datetime(2011, 7, 5, 1, 2, 30), 2.0),\n",
       " (48392341, 101, Decimal('14000.00'), datetime.datetime(2016, 7, 3, 2, 2, 30), 0.0),\n",
       " (48392342, 101, Decimal('8000.00'), datetime.datetime(2016, 8, 3, 6, 2, 30), 31.0),\n",
       " (48392343, 101, Decimal('7777.00'), datetime.datetime(2016, 8, 3, 7, 2, 30), 31.0),\n",
       " (39345112, 103, Decimal('2346.00'), datetime.datetime(2017, 4, 12, 7, 2, 30), 0.0),\n",
       " (39345113, 103, Decimal('1488.00'), datetime.datetime(2017, 4, 12, 8, 2, 30), 0.0),\n",
       " (39345113, 103, Decimal('1703.00'), datetime.datetime(2018, 4, 12, 8, 2, 30), 365.0),\n",
       " (99911135, 104, Decimal('100000.00'), datetime.datetime(2022, 7, 3, 4, 4, 4), 0.0)]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "transaction_id, \n",
    "customer_id, \n",
    "amount_rur, \n",
    "transaction_dttm,\n",
    "DATE_PART('day', transaction_dttm - firts_trn) as days_count\n",
    "\n",
    "FROM (\n",
    "\tSELECT \n",
    "\ttransaction_id, customer_id, amount_rur, transaction_dttm\n",
    "    \n",
    "--если дата первая, значит она должна быть минимальной, самой первой, самой молодой по timestamp\n",
    "\t,MIN(transaction_dttm) OVER(PARTITION BY customer_id ORDER BY transaction_dttm) AS firts_trn \n",
    "\tFROM transaction\n",
    "    ) \n",
    "    transaction;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "907c29b1",
   "metadata": {},
   "source": [
    "#### Задача 2\n",
    "Для каждой транзакции вывести кол-во дней, которые прошли с предыдущей транзакции по клиенту, дату и сумму предыдущей транзакции. \n",
    "На выходе: transaction_id, customer_id, amount_rur, transaction_dttm, days_count, prev_transaction_dttm, prev_amount_rur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "0479e4ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>amount_rur</th>\n",
       "        <th>transaction_dttm</th>\n",
       "        <th>days_count</th>\n",
       "        <th>prev_transaction_dttm</th>\n",
       "        <th>prev_amount_rur</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345678</td>\n",
       "        <td>100</td>\n",
       "        <td>1200.00</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345679</td>\n",
       "        <td>100</td>\n",
       "        <td>10000.00</td>\n",
       "        <td>2011-07-04 01:02:30</td>\n",
       "        <td>1.0</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>1200.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345680</td>\n",
       "        <td>100</td>\n",
       "        <td>3000.00</td>\n",
       "        <td>2011-07-05 01:02:30</td>\n",
       "        <td>1.0</td>\n",
       "        <td>2011-07-04 01:02:30</td>\n",
       "        <td>10000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392341</td>\n",
       "        <td>101</td>\n",
       "        <td>14000.00</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392342</td>\n",
       "        <td>101</td>\n",
       "        <td>8000.00</td>\n",
       "        <td>2016-08-03 06:02:30</td>\n",
       "        <td>31.0</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>14000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392343</td>\n",
       "        <td>101</td>\n",
       "        <td>7777.00</td>\n",
       "        <td>2016-08-03 07:02:30</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2016-08-03 06:02:30</td>\n",
       "        <td>8000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345112</td>\n",
       "        <td>103</td>\n",
       "        <td>2346.00</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1488.00</td>\n",
       "        <td>2017-04-12 08:02:30</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>2346.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1703.00</td>\n",
       "        <td>2018-04-12 08:02:30</td>\n",
       "        <td>365.0</td>\n",
       "        <td>2017-04-12 08:02:30</td>\n",
       "        <td>1488.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99911135</td>\n",
       "        <td>104</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>2022-07-03 04:04:04</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12345678, 100, Decimal('1200.00'), datetime.datetime(2011, 7, 3, 1, 1, 30), None, None, None),\n",
       " (12345679, 100, Decimal('10000.00'), datetime.datetime(2011, 7, 4, 1, 2, 30), 1.0, datetime.datetime(2011, 7, 3, 1, 1, 30), Decimal('1200.00')),\n",
       " (12345680, 100, Decimal('3000.00'), datetime.datetime(2011, 7, 5, 1, 2, 30), 1.0, datetime.datetime(2011, 7, 4, 1, 2, 30), Decimal('10000.00')),\n",
       " (48392341, 101, Decimal('14000.00'), datetime.datetime(2016, 7, 3, 2, 2, 30), None, None, None),\n",
       " (48392342, 101, Decimal('8000.00'), datetime.datetime(2016, 8, 3, 6, 2, 30), 31.0, datetime.datetime(2016, 7, 3, 2, 2, 30), Decimal('14000.00')),\n",
       " (48392343, 101, Decimal('7777.00'), datetime.datetime(2016, 8, 3, 7, 2, 30), 0.0, datetime.datetime(2016, 8, 3, 6, 2, 30), Decimal('8000.00')),\n",
       " (39345112, 103, Decimal('2346.00'), datetime.datetime(2017, 4, 12, 7, 2, 30), None, None, None),\n",
       " (39345113, 103, Decimal('1488.00'), datetime.datetime(2017, 4, 12, 8, 2, 30), 0.0, datetime.datetime(2017, 4, 12, 7, 2, 30), Decimal('2346.00')),\n",
       " (39345113, 103, Decimal('1703.00'), datetime.datetime(2018, 4, 12, 8, 2, 30), 365.0, datetime.datetime(2017, 4, 12, 8, 2, 30), Decimal('1488.00')),\n",
       " (99911135, 104, Decimal('100000.00'), datetime.datetime(2022, 7, 3, 4, 4, 4), None, None, None)]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "transaction_id, \n",
    "customer_id, \n",
    "amount_rur, \n",
    "transaction_dttm, \n",
    "DATE_PART('day', transaction_dttm - prev_transaction_dttm) as days_count,\n",
    "prev_transaction_dttm,\n",
    "prev_amount_rur\n",
    "\n",
    "FROM (\n",
    "\tSELECT \n",
    "\ttransaction_id, customer_id, amount_rur, transaction_dttm\n",
    "\t,LAG(transaction_dttm) OVER(PARTITION BY customer_id ORDER BY transaction_dttm) AS prev_transaction_dttm\n",
    "\t,LAG(amount_rur) OVER(PARTITION BY customer_id) AS prev_amount_rur\n",
    "\tFROM transaction\n",
    "    ) \n",
    "\ttransaction;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bcf7f78",
   "metadata": {},
   "source": [
    "#### Задача 3\n",
    "Для кaждой трaнзaкции посчитать количество днeй, которые прошли с момента первой транзакции по этому клиенту, и проставить флаг, относится ли транзакция к клиенту, начавшему обслуживаться в банке в текущем году. \n",
    "На выходе: transaction_id, customer_id, amount_rur, transaction_dttm, days_count, new_cust_flg.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "311e5b07",
   "metadata": {},
   "source": [
    "Покажу сначала подзапрос. В подзапросе я джойню таблицу customer и извлекаю fist_tnrs (первую транзакцию) чтобы на основании этих данных проводить условие простановки флага."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "4e047c67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>amount_rur</th>\n",
       "        <th>transaction_dttm</th>\n",
       "        <th>first_trns</th>\n",
       "        <th>start_dttm</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345679</td>\n",
       "        <td>100</td>\n",
       "        <td>10000.00</td>\n",
       "        <td>2011-07-04 01:02:30</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>2011-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345680</td>\n",
       "        <td>100</td>\n",
       "        <td>3000.00</td>\n",
       "        <td>2011-07-05 01:02:30</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>2011-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345678</td>\n",
       "        <td>100</td>\n",
       "        <td>1200.00</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>2011-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392342</td>\n",
       "        <td>101</td>\n",
       "        <td>8000.00</td>\n",
       "        <td>2016-08-03 06:02:30</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>2016-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392343</td>\n",
       "        <td>101</td>\n",
       "        <td>7777.00</td>\n",
       "        <td>2016-08-03 07:02:30</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>2016-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392341</td>\n",
       "        <td>101</td>\n",
       "        <td>14000.00</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>2016-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1488.00</td>\n",
       "        <td>2017-04-12 08:02:30</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>2017-03-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345112</td>\n",
       "        <td>103</td>\n",
       "        <td>2346.00</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>2017-03-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1703.00</td>\n",
       "        <td>2018-04-12 08:02:30</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>2017-03-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99911135</td>\n",
       "        <td>104</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>2022-07-03 04:04:04</td>\n",
       "        <td>2022-07-03 04:04:04</td>\n",
       "        <td>2022-07-01 00:00:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12345679, 100, Decimal('10000.00'), datetime.datetime(2011, 7, 4, 1, 2, 30), datetime.datetime(2011, 7, 3, 1, 1, 30), datetime.datetime(2011, 7, 1, 0, 0)),\n",
       " (12345680, 100, Decimal('3000.00'), datetime.datetime(2011, 7, 5, 1, 2, 30), datetime.datetime(2011, 7, 3, 1, 1, 30), datetime.datetime(2011, 7, 1, 0, 0)),\n",
       " (12345678, 100, Decimal('1200.00'), datetime.datetime(2011, 7, 3, 1, 1, 30), datetime.datetime(2011, 7, 3, 1, 1, 30), datetime.datetime(2011, 7, 1, 0, 0)),\n",
       " (48392342, 101, Decimal('8000.00'), datetime.datetime(2016, 8, 3, 6, 2, 30), datetime.datetime(2016, 7, 3, 2, 2, 30), datetime.datetime(2016, 7, 1, 0, 0)),\n",
       " (48392343, 101, Decimal('7777.00'), datetime.datetime(2016, 8, 3, 7, 2, 30), datetime.datetime(2016, 7, 3, 2, 2, 30), datetime.datetime(2016, 7, 1, 0, 0)),\n",
       " (48392341, 101, Decimal('14000.00'), datetime.datetime(2016, 7, 3, 2, 2, 30), datetime.datetime(2016, 7, 3, 2, 2, 30), datetime.datetime(2016, 7, 1, 0, 0)),\n",
       " (39345113, 103, Decimal('1488.00'), datetime.datetime(2017, 4, 12, 8, 2, 30), datetime.datetime(2017, 4, 12, 7, 2, 30), datetime.datetime(2017, 3, 12, 0, 0)),\n",
       " (39345112, 103, Decimal('2346.00'), datetime.datetime(2017, 4, 12, 7, 2, 30), datetime.datetime(2017, 4, 12, 7, 2, 30), datetime.datetime(2017, 3, 12, 0, 0)),\n",
       " (39345113, 103, Decimal('1703.00'), datetime.datetime(2018, 4, 12, 8, 2, 30), datetime.datetime(2017, 4, 12, 7, 2, 30), datetime.datetime(2017, 3, 12, 0, 0)),\n",
       " (99911135, 104, Decimal('100000.00'), datetime.datetime(2022, 7, 3, 4, 4, 4), datetime.datetime(2022, 7, 3, 4, 4, 4), datetime.datetime(2022, 7, 1, 0, 0))]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "transaction.transaction_id, \n",
    "transaction.customer_id, \n",
    "transaction.amount_rur, \n",
    "transaction.transaction_dttm\n",
    ",MIN(transaction.transaction_dttm) OVER(PARTITION BY transaction.customer_id) AS first_trns \n",
    ",customer.start_dttm\n",
    "\n",
    "FROM transaction \n",
    "INNER JOIN customer\n",
    "ON customer.customer_id=transaction.customer_id;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd0cb56f",
   "metadata": {},
   "source": [
    "Теперь основной селект:\n",
    "\n",
    "\n",
    "извлекает количество дней с первой транзакции\n",
    "\n",
    "```SQL\n",
    "DATE_PART('day', transaction_dttm - first_trns) as days_count \n",
    "```\n",
    "\n",
    "Простановка флага по принципу: если текущая дата - первая дата = 0, значит TRUE, значит клиент появился в текущем году\n",
    "Во всех иных случаях ставить false.\n",
    "\n",
    "```SQL\n",
    ",case when (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', start_dttm) = 0) THEN 'TRUE'\n",
    "\telse 'false'\n",
    "\tend as new_cust_flg\n",
    "\n",
    "```\n",
    "\n",
    "Конечно для предобработки данных имеет смысл указать условие при, котором текущая дата вдруг меньше даты регистрации клиента, но я думаю программисты не дураки, отфиксили этот момент давным давно."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "5deb48d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/tested\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>amount_rur</th>\n",
       "        <th>transaction_dttm</th>\n",
       "        <th>days_count</th>\n",
       "        <th>new_cust_flg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345679</td>\n",
       "        <td>100</td>\n",
       "        <td>10000.00</td>\n",
       "        <td>2011-07-04 01:02:30</td>\n",
       "        <td>1.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345680</td>\n",
       "        <td>100</td>\n",
       "        <td>3000.00</td>\n",
       "        <td>2011-07-05 01:02:30</td>\n",
       "        <td>2.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345678</td>\n",
       "        <td>100</td>\n",
       "        <td>1200.00</td>\n",
       "        <td>2011-07-03 01:01:30</td>\n",
       "        <td>0.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392342</td>\n",
       "        <td>101</td>\n",
       "        <td>8000.00</td>\n",
       "        <td>2016-08-03 06:02:30</td>\n",
       "        <td>31.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392343</td>\n",
       "        <td>101</td>\n",
       "        <td>7777.00</td>\n",
       "        <td>2016-08-03 07:02:30</td>\n",
       "        <td>31.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48392341</td>\n",
       "        <td>101</td>\n",
       "        <td>14000.00</td>\n",
       "        <td>2016-07-03 02:02:30</td>\n",
       "        <td>0.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1488.00</td>\n",
       "        <td>2017-04-12 08:02:30</td>\n",
       "        <td>0.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345112</td>\n",
       "        <td>103</td>\n",
       "        <td>2346.00</td>\n",
       "        <td>2017-04-12 07:02:30</td>\n",
       "        <td>0.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>39345113</td>\n",
       "        <td>103</td>\n",
       "        <td>1703.00</td>\n",
       "        <td>2018-04-12 08:02:30</td>\n",
       "        <td>365.0</td>\n",
       "        <td>false</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>99911135</td>\n",
       "        <td>104</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>2022-07-03 04:04:04</td>\n",
       "        <td>0.0</td>\n",
       "        <td>TRUE</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12345679, 100, Decimal('10000.00'), datetime.datetime(2011, 7, 4, 1, 2, 30), 1.0, 'false'),\n",
       " (12345680, 100, Decimal('3000.00'), datetime.datetime(2011, 7, 5, 1, 2, 30), 2.0, 'false'),\n",
       " (12345678, 100, Decimal('1200.00'), datetime.datetime(2011, 7, 3, 1, 1, 30), 0.0, 'false'),\n",
       " (48392342, 101, Decimal('8000.00'), datetime.datetime(2016, 8, 3, 6, 2, 30), 31.0, 'false'),\n",
       " (48392343, 101, Decimal('7777.00'), datetime.datetime(2016, 8, 3, 7, 2, 30), 31.0, 'false'),\n",
       " (48392341, 101, Decimal('14000.00'), datetime.datetime(2016, 7, 3, 2, 2, 30), 0.0, 'false'),\n",
       " (39345113, 103, Decimal('1488.00'), datetime.datetime(2017, 4, 12, 8, 2, 30), 0.0, 'false'),\n",
       " (39345112, 103, Decimal('2346.00'), datetime.datetime(2017, 4, 12, 7, 2, 30), 0.0, 'false'),\n",
       " (39345113, 103, Decimal('1703.00'), datetime.datetime(2018, 4, 12, 8, 2, 30), 365.0, 'false'),\n",
       " (99911135, 104, Decimal('100000.00'), datetime.datetime(2022, 7, 3, 4, 4, 4), 0.0, 'TRUE')]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "transaction_id, customer_id, amount_rur, transaction_dttm \n",
    ",DATE_PART('day', transaction_dttm - first_trns) as days_count\n",
    "\n",
    ",case when (DATE_PART('year', CURRENT_DATE) - DATE_PART('year', start_dttm) = 0) THEN 'TRUE'\n",
    "\telse 'false'\n",
    "\tend as new_cust_flg\n",
    "\n",
    "FROM \n",
    "\t(\n",
    "\tSELECT \n",
    "\ttransaction.transaction_id, \n",
    "\ttransaction.customer_id, \n",
    "\ttransaction.amount_rur, \n",
    "\ttransaction.transaction_dttm\n",
    "\t,MIN(transaction.transaction_dttm) OVER(PARTITION BY transaction.customer_id) AS first_trns \n",
    "\t,customer.start_dttm\n",
    "\tFROM transaction \n",
    "\tINNER JOIN customer\n",
    "\tON customer.customer_id=transaction.customer_id\n",
    "\t) transaction;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41b97264",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
